package com.ai.view;

import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.web.servlet.view.document.AbstractExcelView;

import com.ai.mmd.reviewed.vo.SbmxinfoExtVO;

/**
 * 导出特色航线Excel视图
 * 
 * @author Andy
 *
 */
public class GkViewExcel extends AbstractExcelView {

	@SuppressWarnings("unchecked")
	@Override
	protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		String excelName = "港口出入记录.xls";
		// 设置response方式,使执行此controller时候自动出现下载页面,而非直接使用excel打开
		response.setContentType("APPLICATION/OCTET-STREAM");
		response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(excelName, "UTF-8"));

		//标题样式 start
		HSSFCellStyle headStyle = workbook.createCellStyle();
		headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		headStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框    
		headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框    
		headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框    
		headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框  

		HSSFFont titlefont = workbook.createFont();
		titlefont.setFontHeightInPoints((short) 22);
		headStyle.setFont(titlefont);
		//标题样式 end
		
		//单元格样式
		HSSFCellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框    
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框    
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框    
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框  
		
		// 产生Excel表头
		HSSFSheet sheetwh = workbook.createSheet("武汉");
		HSSFSheet sheetyl = workbook.createSheet("阳逻");
		HSSFSheet sheetfirst = workbook.createSheet("阳逻一期");
		HSSFSheet sheetsecond = workbook.createSheet("阳逻二期");
		HSSFSheet sheetthirth = workbook.createSheet("阳逻三期");

		HSSFCellStyle titleStyle = workbook.createCellStyle();
		titleStyle.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
		titleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
		titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

		HSSFFont font = workbook.createFont();
		font.setFontHeightInPoints((short) 11);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
		titleStyle.setFont(font);
		
		//添加标题
		HSSFRow titlewh = sheetwh.createRow(0);
		titlewh.setHeight((short) 500);
		HSSFCell hsstitlewh = titlewh.createCell(0);
		hsstitlewh.setCellValue("港口出入记录");
		hsstitlewh.setCellStyle(headStyle);
		sheetwh.addMergedRegion(new CellRangeAddress(0, 0, 0, 5));
		
		HSSFRow titleyl = sheetyl.createRow(0);
		titleyl.setHeight((short) 500);
		HSSFCell hsstitleyl = titleyl.createCell(0);
		hsstitleyl.setCellValue("港口出入记录");
		hsstitleyl.setCellStyle(headStyle);
		sheetyl.addMergedRegion(new CellRangeAddress(0, 0, 0, 5));
		
		HSSFRow titlefirst = sheetfirst.createRow(0);
		titlefirst.setHeight((short) 500);
		HSSFCell hsstitlefirst = titlefirst.createCell(0);
		hsstitlefirst.setCellValue("港口出入记录");
		hsstitlefirst.setCellStyle(headStyle);
		sheetfirst.addMergedRegion(new CellRangeAddress(0, 0, 0, 5));
		
		HSSFRow titlesecond = sheetsecond.createRow(0);
		titlesecond.setHeight((short) 500);
		HSSFCell hsstitlesecond = titlesecond.createCell(0);
		hsstitlesecond.setCellValue("港口出入记录");
		hsstitlesecond.setCellStyle(headStyle);
		sheetsecond.addMergedRegion(new CellRangeAddress(0, 0, 0, 5));
		
		HSSFRow titlethirth = sheetthirth.createRow(0);
		titlethirth.setHeight((short) 500);
		HSSFCell hsstitlethirth = titlethirth.createCell(0);
		hsstitlethirth.setCellValue("港口出入记录");
		hsstitlethirth.setCellStyle(headStyle);
		sheetthirth.addMergedRegion(new CellRangeAddress(0, 0, 0, 5));

		HSSFRow headwh = sheetwh.createRow(1);
		HSSFRow headyl = sheetyl.createRow(1);
		HSSFRow headfirst = sheetfirst.createRow(1);
		HSSFRow headsecond = sheetsecond.createRow(1);
		HSSFRow headthirth = sheetthirth.createRow(1);
		headwh.setHeight((short) 400);
		headyl.setHeight((short) 400);
		headfirst.setHeight((short) 400);
		headsecond.setHeight((short) 400);
		headthirth.setHeight((short) 400);
		
		for(int i=1;i<6;i++){
			sheetwh.setColumnWidth(i, 5000);
			sheetyl.setColumnWidth(i, 5000);
			sheetfirst.setColumnWidth(i, 5000);
			sheetsecond.setColumnWidth(i, 5000);
			sheetthirth.setColumnWidth(i, 5000);
		}	
		
		HSSFCell h_whxl = headwh.createCell(0);
		HSSFCell h_ylxl = headyl.createCell(0);
		HSSFCell h_firstxl = headfirst.createCell(0);
		HSSFCell h_secondxl = headsecond.createCell(0);
		HSSFCell h_thirthxl = headthirth.createCell(0);
		h_whxl.setCellValue("序号");
		h_ylxl.setCellValue("序号");
		h_firstxl.setCellValue("序号");
		h_secondxl.setCellValue("序号");
		h_thirthxl.setCellValue("序号");
		h_whxl.setCellStyle(titleStyle);
		h_ylxl.setCellStyle(titleStyle);
		h_firstxl.setCellStyle(titleStyle);
		h_secondxl.setCellStyle(titleStyle);
		h_thirthxl.setCellStyle(titleStyle);
		
		HSSFCell h_whcmhc = headwh.createCell(1);
		HSSFCell h_ylcmhc = headyl.createCell(1);
		HSSFCell h_firstcmhc = headfirst.createCell(1);
		HSSFCell h_secondcmhc = headsecond.createCell(1);
		HSSFCell h_thirthcmhc = headthirth.createCell(1);
		h_whcmhc.setCellValue("船名航次");
		h_ylcmhc.setCellValue("船名航次");
		h_firstcmhc.setCellValue("船名航次");
		h_secondcmhc.setCellValue("船名航次");
		h_thirthcmhc.setCellValue("船名航次");
		h_whcmhc.setCellStyle(titleStyle);
		h_ylcmhc.setCellStyle(titleStyle);
		h_firstcmhc.setCellStyle(titleStyle);
		h_secondcmhc.setCellStyle(titleStyle);
		h_thirthcmhc.setCellStyle(titleStyle);

		HSSFCell h_whhydh = headwh.createCell(2);
		HSSFCell h_ylhydh = headyl.createCell(2);
		HSSFCell h_firsthydh = headfirst.createCell(2);
		HSSFCell h_secondhydh = headsecond.createCell(2);
		HSSFCell h_thirthhydh = headthirth.createCell(2);
		h_whhydh.setCellValue("货运单号");
		h_ylhydh.setCellValue("货运单号");
		h_firsthydh.setCellValue("货运单号");
		h_secondhydh.setCellValue("货运单号");
		h_thirthhydh.setCellValue("货运单号");
		h_whhydh.setCellStyle(titleStyle);
		h_ylhydh.setCellStyle(titleStyle);
		h_firsthydh.setCellStyle(titleStyle);
		h_secondhydh.setCellStyle(titleStyle);
		h_thirthhydh.setCellStyle(titleStyle);
		
		HSSFCell h_whxh = headwh.createCell(3);
		HSSFCell h_ylxh = headyl.createCell(3);
		HSSFCell h_firstxh = headfirst.createCell(3);
		HSSFCell h_secondxh = headsecond.createCell(3);
		HSSFCell h_thirthxh = headthirth.createCell(3);
		h_whxh.setCellValue("箱号");
		h_ylxh.setCellValue("箱号");
		h_firstxh.setCellValue("箱号");
		h_secondxh.setCellValue("箱号");
		h_thirthxh.setCellValue("箱号");
		h_whxh.setCellStyle(titleStyle);
		h_ylxh.setCellStyle(titleStyle);
		h_firstxh.setCellStyle(titleStyle);
		h_secondxh.setCellStyle(titleStyle);
		h_thirthxh.setCellStyle(titleStyle);

		HSSFCell h_whfh = headwh.createCell(4);
		HSSFCell h_ylfh = headyl.createCell(4);
		HSSFCell h_firstfh = headfirst.createCell(4);
		HSSFCell h_secondfh = headsecond.createCell(4);
		HSSFCell h_thirthfh = headthirth.createCell(4);
		h_whfh.setCellValue("封号");
		h_ylfh.setCellValue("封号");
		h_firstfh.setCellValue("封号");
		h_secondfh.setCellValue("封号");
		h_thirthfh.setCellValue("封号");
		h_whfh.setCellStyle(titleStyle);
		h_ylfh.setCellStyle(titleStyle);
		h_firstfh.setCellStyle(titleStyle);
		h_secondfh.setCellStyle(titleStyle);
		h_thirthfh.setCellStyle(titleStyle);

		HSSFCell h_whsj = headwh.createCell(5);
		HSSFCell h_ylsj = headyl.createCell(5);
		HSSFCell h_firstsj = headfirst.createCell(5);
		HSSFCell h_secondsj = headsecond.createCell(5);
		HSSFCell h_thirthsj = headthirth.createCell(5);
		h_whsj.setCellValue("出/入码头时间");
		h_ylsj.setCellValue("出/入码头时间");
		h_firstsj.setCellValue("出/入码头时间");
		h_secondsj.setCellValue("出/入码头时间");
		h_thirthsj.setCellValue("出/入码头时间");
		h_whsj.setCellStyle(titleStyle);
		h_ylsj.setCellStyle(titleStyle);
		h_firstsj.setCellStyle(titleStyle);
		h_secondsj.setCellStyle(titleStyle);
		h_thirthsj.setCellStyle(titleStyle);

		List<SbmxinfoExtVO> gkwh = (List<SbmxinfoExtVO>) model.get("gkwh");
		List<SbmxinfoExtVO> gkyl = (List<SbmxinfoExtVO>) model.get("gkyl");
		List<SbmxinfoExtVO> gkfirst = (List<SbmxinfoExtVO>) model.get("gkfirst");
		List<SbmxinfoExtVO> gksecond = (List<SbmxinfoExtVO>) model.get("gksecond");
		List<SbmxinfoExtVO> gkthirth = (List<SbmxinfoExtVO>) model.get("gkthirth");
		
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");

		for (int i=2;i<=gkwh.size()+1;i++) {
			HSSFRow row = sheetwh.createRow(i);
			
			//序号
			HSSFCell r_xl = row.createCell(0);
			HSSFRichTextString richxl = new HSSFRichTextString(String.valueOf(i-1));
			r_xl.setCellValue(richxl);
			r_xl.setCellStyle(cellStyle);
			
			//船名航次
			HSSFCell r_cmhc = row.createCell(1);
			HSSFRichTextString richcmhc = new HSSFRichTextString(gkwh.get(i-2).getCmhc());
			r_cmhc.setCellValue(richcmhc);
			r_cmhc.setCellStyle(cellStyle);
			
			//货运单号
			HSSFCell r_hydh = row.createCell(2);
			HSSFRichTextString richhydh = new HSSFRichTextString(gkwh.get(i-2).getHwydh());
			r_hydh.setCellValue(richhydh);
			r_hydh.setCellStyle(cellStyle);
			
			//箱号
			HSSFCell r_xh = row.createCell(3);
			HSSFRichTextString richxh = new HSSFRichTextString(gkwh.get(i-2).getXh());
			r_xh.setCellValue(richxh);
			r_xh.setCellStyle(cellStyle);
			
			//封号
			HSSFCell r_fh = row.createCell(4);
			HSSFRichTextString richfh = new HSSFRichTextString(gkwh.get(i-2).getFh());
			r_fh.setCellValue(richfh);
			r_fh.setCellStyle(cellStyle);
			
			//出/入码头时间
			HSSFCell r_crmtsj = row.createCell(5);
			HSSFRichTextString richcrmtsj = new HSSFRichTextString(format.format(gkwh.get(i-2).getCrgksj()));
			r_crmtsj.setCellValue(richcrmtsj);
			r_crmtsj.setCellStyle(cellStyle);
		}
		
		for (int i=2;i<=gkyl.size()+1;i++) {
			HSSFRow row = sheetyl.createRow(i);
			
			//序号
			HSSFCell r_xl = row.createCell(0);
			HSSFRichTextString richxl = new HSSFRichTextString(String.valueOf(i-1));
			r_xl.setCellValue(richxl);
			r_xl.setCellStyle(cellStyle);
			
			//船名航次
			HSSFCell r_cmhc = row.createCell(1);
			HSSFRichTextString richcmhc = new HSSFRichTextString(gkyl.get(i-2).getCmhc());
			r_cmhc.setCellValue(richcmhc);
			r_cmhc.setCellStyle(cellStyle);
			
			//货运单号
			HSSFCell r_hydh = row.createCell(2);
			HSSFRichTextString richhydh = new HSSFRichTextString(gkyl.get(i-2).getHwydh());
			r_hydh.setCellValue(richhydh);
			r_hydh.setCellStyle(cellStyle);
			
			//箱号
			HSSFCell r_xh = row.createCell(3);
			HSSFRichTextString richxh = new HSSFRichTextString(gkyl.get(i-2).getXh());
			r_xh.setCellValue(richxh);
			r_xh.setCellStyle(cellStyle);
			
			//封号
			HSSFCell r_fh = row.createCell(4);
			HSSFRichTextString richfh = new HSSFRichTextString(gkyl.get(i-2).getFh());
			r_fh.setCellValue(richfh);
			r_fh.setCellStyle(cellStyle);
			
			//出/入码头时间
			HSSFCell r_crmtsj = row.createCell(5);
			HSSFRichTextString richcrmtsj = new HSSFRichTextString(format.format(gkyl.get(i-2).getCrgksj()));
			r_crmtsj.setCellValue(richcrmtsj);
			r_crmtsj.setCellStyle(cellStyle);
		}
		
		for (int i=2;i<=gkfirst.size()+1;i++) {
			HSSFRow row = sheetfirst.createRow(i);
			
			//序号
			HSSFCell r_xl = row.createCell(0);
			HSSFRichTextString richxl = new HSSFRichTextString(String.valueOf(i-1));
			r_xl.setCellValue(richxl);
			r_xl.setCellStyle(cellStyle);
			
			//船名航次
			HSSFCell r_cmhc = row.createCell(1);
			HSSFRichTextString richcmhc = new HSSFRichTextString(gkfirst.get(i-2).getCmhc());
			r_cmhc.setCellValue(richcmhc);
			r_cmhc.setCellStyle(cellStyle);
			
			//货运单号
			HSSFCell r_hydh = row.createCell(2);
			HSSFRichTextString richhydh = new HSSFRichTextString(gkfirst.get(i-2).getHwydh());
			r_hydh.setCellValue(richhydh);
			r_hydh.setCellStyle(cellStyle);
			
			//箱号
			HSSFCell r_xh = row.createCell(3);
			HSSFRichTextString richxh = new HSSFRichTextString(gkfirst.get(i-2).getXh());
			r_xh.setCellValue(richxh);
			r_xh.setCellStyle(cellStyle);
			
			//封号
			HSSFCell r_fh = row.createCell(4);
			HSSFRichTextString richfh = new HSSFRichTextString(gkfirst.get(i-2).getFh());
			r_fh.setCellValue(richfh);
			r_fh.setCellStyle(cellStyle);
			
			//出/入码头时间
			HSSFCell r_crmtsj = row.createCell(5);
			HSSFRichTextString richcrmtsj = new HSSFRichTextString(format.format(gkfirst.get(i-2).getCrgksj()));
			r_crmtsj.setCellValue(richcrmtsj);
			r_crmtsj.setCellStyle(cellStyle);
		}
		
		for (int i=2;i<=gksecond.size()+1;i++) {
			HSSFRow row = sheetsecond.createRow(i);
			
			//序号
			HSSFCell r_xl = row.createCell(0);
			HSSFRichTextString richxl = new HSSFRichTextString(String.valueOf(i-1));
			r_xl.setCellValue(richxl);
			r_xl.setCellStyle(cellStyle);
			
			//船名航次
			HSSFCell r_cmhc = row.createCell(1);
			HSSFRichTextString richcmhc = new HSSFRichTextString(gksecond.get(i-2).getCmhc());
			r_cmhc.setCellValue(richcmhc);
			r_cmhc.setCellStyle(cellStyle);
			
			//货运单号
			HSSFCell r_hydh = row.createCell(2);
			HSSFRichTextString richhydh = new HSSFRichTextString(gksecond.get(i-2).getHwydh());
			r_hydh.setCellValue(richhydh);
			r_hydh.setCellStyle(cellStyle);
			
			//箱号
			HSSFCell r_xh = row.createCell(3);
			HSSFRichTextString richxh = new HSSFRichTextString(gksecond.get(i-2).getXh());
			r_xh.setCellValue(richxh);
			r_xh.setCellStyle(cellStyle);
			
			//封号
			HSSFCell r_fh = row.createCell(4);
			HSSFRichTextString richfh = new HSSFRichTextString(gksecond.get(i-2).getFh());
			r_fh.setCellValue(richfh);
			r_fh.setCellStyle(cellStyle);
			
			//出/入码头时间
			HSSFCell r_crmtsj = row.createCell(5);
			HSSFRichTextString richcrmtsj = new HSSFRichTextString(format.format(gksecond.get(i-2).getCrgksj()));
			r_crmtsj.setCellValue(richcrmtsj);
			r_crmtsj.setCellStyle(cellStyle);
		}
		
		for (int i=2;i<=gkthirth.size()+1;i++) {
			HSSFRow row = sheetthirth.createRow(i);
			
			//序号
			HSSFCell r_xl = row.createCell(0);
			HSSFRichTextString richxl = new HSSFRichTextString(String.valueOf(i-1));
			r_xl.setCellValue(richxl);
			r_xl.setCellStyle(cellStyle);
			
			//船名航次
			HSSFCell r_cmhc = row.createCell(1);
			HSSFRichTextString richcmhc = new HSSFRichTextString(gkthirth.get(i-2).getCmhc());
			r_cmhc.setCellValue(richcmhc);
			r_cmhc.setCellStyle(cellStyle);
			
			//货运单号
			HSSFCell r_hydh = row.createCell(2);
			HSSFRichTextString richhydh = new HSSFRichTextString(gkthirth.get(i-2).getHwydh());
			r_hydh.setCellValue(richhydh);
			r_hydh.setCellStyle(cellStyle);
			
			//箱号
			HSSFCell r_xh = row.createCell(3);
			HSSFRichTextString richxh = new HSSFRichTextString(gkthirth.get(i-2).getXh());
			r_xh.setCellValue(richxh);
			r_xh.setCellStyle(cellStyle);
			
			//封号
			HSSFCell r_fh = row.createCell(4);
			HSSFRichTextString richfh = new HSSFRichTextString(gkthirth.get(i-2).getFh());
			r_fh.setCellValue(richfh);
			r_fh.setCellStyle(cellStyle);
			
			//出/入码头时间
			HSSFCell r_crmtsj = row.createCell(5);
			HSSFRichTextString richcrmtsj = new HSSFRichTextString(format.format(gkthirth.get(i-2).getCrgksj()));
			r_crmtsj.setCellValue(richcrmtsj);
			r_crmtsj.setCellStyle(cellStyle);
		}
	}
}
